import pandas as pd
import numpy as np
from decimal import Decimal
import pymysql as psq
from sqlalchemy import create_engine

pd.set_option("display.max_columns", 100)
pd.set_option("display.max_rows", 1000)


def clearData(path):
    computer_vision = pd.read_csv(path)
    del computer_vision["标题"]
    del computer_vision["名称"]
    del computer_vision["时间"]
    # print(computer_vision)
    computer_vision["薪资"] = computer_vision["sal"]
    del computer_vision["sal"]

    # print(computer_vision["薪资"])
    # computer_vision["薪资"].info()
    # print(computer_vision["薪资"])
    #
    def payrollPreprocessing(data):
        str1 = ""
        data = str(data)
        if data[-3:] == "万/月":
            x = data.replace("万/月", "")
            x = x.split("-")
            for i in range(len(x)):
                x[i] = float(x[i]) * 10
                x[i] = Decimal(x[i]).quantize(Decimal("0.00"))
                str1 += str(x[i]) + "-"
            return str1[:-2]
        elif data[-3:] == "千/年":
            x = data.replace("千/年", "")
            x = x.split("-")
            for i in range(len(x)):
                x[i] = float(x[i]) / 12
                x[i] = Decimal(x[i]).quantize(Decimal("0.00"))
                str1 += str(x[i]) + "-"
            return str1[:-2]
        elif data[-3:] == "千/月":
            x = data.replace("千/月", "")
            return x
        elif data[-3:] == "万/年":
            x = data.replace("万/年", "")
            x = x.split("-")
            for i in range(len(x)):
                x[i] = float(x[i]) / 12 * 10
                x[i] = Decimal(x[i]).quantize(Decimal("0.00"))
                str1 += str(x[i]) + "-"
            return str1[:-2]
        else:
            return np.nan

    computer_vision["薪资"] = computer_vision["薪资"].apply(payrollPreprocessing)
    computer_vision.dropna(subset=["薪资"], inplace=True, how="all")
    computer_vision.dropna(axis=0, thresh=4, subset=["标签", "标签1", "标签2", "标签3", "标签4", "标签5", "标签6", ],
                           how="all", inplace=True)
    salary_statistics = computer_vision.groupby("薪资")["薪资"].count()
    # 空值数量
    # print(computer_vision.isnull().sum())
    # 薪资降序输出
    # print(salary_statistics.sort_values(ascending=False))

    # 地区处理
    computer_vision["地区"] = computer_vision["d"]
    computer_vision["经验需求"] = computer_vision["d"]
    computer_vision["学位需求"] = computer_vision["d"]
    del computer_vision["d"]

    # print(computer_vision["经验需求"])

    # 地区提取
    def regionExtraction(data):
        area = data.split("|")
        area = area[0]
        area = area.split("-")
        area = area[0]
        area = area.strip()
        return area

    computer_vision["地区"] = computer_vision["地区"].apply(regionExtraction)

    # print(computer_vision["地区"])

    # 经验需求
    def experienceRequirements(data):
        experience = data.split("|")
        if len(experience) == 3 or len(experience) == 2:
            experience = experience[1]
            experience = experience.strip()
            return experience
        else:
            return "无需经验"

    computer_vision["经验需求"] = computer_vision["经验需求"].apply(experienceRequirements)

    # print(computer_vision["经验需求"])

    # 学位需求
    def degreeRequirements(data):
        degree = data.split("|")
        if len(degree) == 3:
            degree = degree[2]
            degree = degree.strip()
            return degree
        else:
            return "本科"

    computer_vision["学位需求"] = computer_vision["学位需求"].apply(degreeRequirements)
    # print(computer_vision["经验需求"])

    # 福利
    welfare = pd.concat([computer_vision["标签"], computer_vision["标签1"], computer_vision["标签2"], computer_vision["标签3"],
                         computer_vision["标签4"], computer_vision["标签5"], computer_vision["标签6"]], axis=1,
                        ignore_index=True)
    welfare.rename(columns={0: "福利0", 1: "福利1", 2: "福利2", 3: "福利3", 4: "福利4", 5: "福利5", 6: "福利6"},
                   inplace=True)
    # print(welfare)

    computer_vision["就业岗位"] = computer_vision["int"]

    # 就业岗位
    def employmentPosition0(data):
        employment = str(data).split("/")
        employment = employment[0].strip()
        return employment

    def employmentPosition1(data):
        employment = data.split("/")
        if len(employment) == 2 or len(employment) == 3:
            employment = employment[1].strip()
            return employment
        else:
            return np.nan

    def employmentPosition2(data):
        employment = data.split("/")
        if len(employment) == 3:
            employment = employment[2].strip()
            return employment
        else:
            return np.nan

    computer_vision["就业岗位0"] = computer_vision["就业岗位"].apply(employmentPosition0)
    computer_vision["就业岗位1"] = computer_vision["就业岗位"].apply(employmentPosition1)
    computer_vision["就业岗位2"] = computer_vision["就业岗位"].apply(employmentPosition2)
    employment_position = pd.concat([computer_vision["就业岗位0"], computer_vision["就业岗位1"], computer_vision["就业岗位2"]],
                                    axis=1)
    # print(employment_position)

    # 公司人数Number of companies
    computer_vision["公司人数"] = computer_vision["dc"]
    computer_vision["公司类型"] = computer_vision["dc"]

    # print(computer_vision["公司人数"])
    def companyNumber(data):
        number = data.split("|")
        if len(number) == 2:
            number = number[1].strip()
            return number
        else:
            return np.nan

    computer_vision["公司人数"] = computer_vision["公司人数"].apply(companyNumber)

    # 公司类型
    # print(computer_vision["公司类型"])
    # print(computer_vision["公司类型"])
    def companyType(data):
        type = data.split("|")
        if len(type) == 2:
            type = type[0].strip()
            return type
        else:
            return np.nan

    computer_vision["公司类型"] = computer_vision["公司类型"].apply(companyType)
    # print(computer_vision["公司类型"])
    new_computer_vision = pd.concat([computer_vision["薪资"], computer_vision["地区"], computer_vision["经验需求"],
                                     computer_vision["就业岗位0"], computer_vision["就业岗位1"], computer_vision["就业岗位2"],
                                     computer_vision["公司人数"], computer_vision["公司类型"], welfare,
                                     computer_vision["学位需求"]], axis=1)
    return new_computer_vision
